package com.sinodata.bsm.center.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import com.sinodata.bsm.center.bean.Condition;
import com.sinodata.bsm.center.bean.NotifyHisBean;
import com.sinodata.bsm.center.bean.Page;
import com.sinodata.bsm.center.dao.BaseDao;
import com.sinodata.bsm.center.dao.NotifyHisDao;
import com.sinodata.bsm.common.vo.NotifyHis;

/**
 * <p>
 * Description: 通知(通知历史)查询
 * </p>
 *
 * @author liulibo
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2012-6-1 PM 1:11:15      liulibo        1.0         To create
 * </p>
 *
 * @since 
 * @see     
 */
@Repository
public class NotifyHisDaoImpl extends BaseDao implements NotifyHisDao {

    private final static Logger logger = LoggerFactory.getLogger(NotifyHisDaoImpl.class);

    /* (non-Javadoc)
     * @see com.sinodata.bsm.center.dao.NotifyDao#findByPage(com.sinodata.bsm.center.bean.Page, com.sinodata.bsm.center.bean.Condition)
     */
    @Override
    public Page<NotifyHisBean> findByPage(Page<NotifyHisBean> page, Condition condition, String resTypeId, List<String> dimensionArray) {
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat simple = new SimpleDateFormat(format);//to_char(e.occur_time,'yyyy-MM-dd hh24:mi')
        StringBuilder sb = new StringBuilder(" select n.id,");
        sb.append(" n.user_,");
        sb.append(" n.notify_mode,");
        sb.append(" to_char(n.time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" n.msg,");
        sb.append(" e.level_,");
        sb.append(" e.status,");
        sb.append(" to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" r.name");
        sb.append(" from notify_his n");
        sb.append(" left outer join event e on e.id = n.event_id");
        sb.append(" left outer join res r on r.id = e.res_id ");
        sb.append(" where 1 = 1 and r.deleted = 0");
        sb.append(getConditonSql(condition));
        sb.append(getResTypeSql(resTypeId));
        sb.append(getDimensionSql(dimensionArray));
        sb.append(getOrderSql(page));
        page.setTotal(getTotal(condition, resTypeId, dimensionArray));
        Query query = getSession().createSQLQuery(sb.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize());
        @SuppressWarnings("unchecked")
        List<Object[]> list = query.list();
        List<NotifyHisBean> result = new ArrayList<NotifyHisBean>();
        for (Object[] obj : list) {
            NotifyHisBean notifyHisBean = new NotifyHisBean();
            NotifyHis notifyHis = new NotifyHis();
            notifyHis.setId(Long.valueOf(String.valueOf(obj[0])));
            notifyHis.setUser(String.valueOf(obj[1]));
            notifyHis.setNotifyMode(Integer.valueOf(String.valueOf(obj[2])));
            try {
                notifyHis.setTime(simple.parse(String.valueOf(obj[3])));
            } catch (ParseException e) {
                logger.error("find notify history bean by page parse time error", e);
            }
            notifyHis.setMsg(String.valueOf(obj[4]));
            notifyHisBean.setEventLevel(Integer.valueOf(String.valueOf(obj[5])));
            notifyHisBean.setEventStatus(Integer.valueOf(String.valueOf(obj[6])));
            try {
                notifyHisBean.setEventOccurTime(simple.parse(String.valueOf(obj[7])));
            } catch (ParseException e) {
                logger.error("find notify history bean by page parse event occur time  error", e);
            }
            notifyHisBean.setResName(String.valueOf(obj[8]));
            notifyHisBean.setNotifyHis(notifyHis);
            result.add(notifyHisBean);
        }
        page.setRows(result);
        return page;
    }

    /*
     * (non-Javadoc)
     * @see com.sinodata.bsm.center.dao.NotifyHisDao#findByCondition(com.sinodata.bsm.center.bean.Condition)
     */
    @Override
    public List<NotifyHisBean> findByCondition(Condition condition, String resTypeId, List<String> dimensionArray) {
        List<NotifyHisBean> result = new ArrayList<NotifyHisBean>();
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat simple = new SimpleDateFormat(format);
        StringBuilder sb = new StringBuilder(" select n.id,");
        sb.append(" n.user_,");
        sb.append(" n.notify_mode,");
        sb.append(" to_char(n.time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" n.msg,");
        sb.append(" e.level_,");
        sb.append(" e.status,");
        sb.append(" to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" res.name");
        sb.append(" from notify_his n");
        sb.append(" left outer join event e on e.id = n.event_id");
        sb.append(" left outer join res res on res.id = e.res_id");
        sb.append(" where 1 = 1 and res.deleted = 0 ");
        sb.append(getConditonSql(condition));
        sb.append(getResTypeSql(resTypeId));
        sb.append(getDimensionSql(dimensionArray));
        sb.append(" order by n.time desc");
        Query query = getSession().createSQLQuery(sb.toString());
        @SuppressWarnings("unchecked")
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            NotifyHisBean notifyHisBean = new NotifyHisBean();
            NotifyHis notifyHis = new NotifyHis();
            notifyHis.setId(Long.valueOf(String.valueOf(obj[0])));
            notifyHis.setUser(String.valueOf(obj[1]));
            notifyHis.setNotifyMode(Integer.valueOf(String.valueOf(obj[2])));
            try {
                notifyHis.setTime(simple.parse(String.valueOf(obj[3])));
            } catch (ParseException e) {
                logger.error("find notify history bean by condition parse time error", e);
            }
            notifyHis.setMsg(String.valueOf(obj[4]));
            notifyHisBean.setEventLevel(Integer.valueOf(String.valueOf(obj[5])));
            notifyHisBean.setEventStatus(Integer.valueOf(String.valueOf(obj[6])));
            try {
                notifyHisBean.setEventOccurTime(simple.parse(String.valueOf(obj[7])));
            } catch (ParseException e) {
                logger.error("find notify history bean by condition parse event occur time  error", e);
            }
            notifyHisBean.setResName(String.valueOf(obj[8]));
            notifyHisBean.setNotifyHis(notifyHis);
            result.add(notifyHisBean);
        }
        return result;
    }

    /**
     * 获取排序sql
     * 
     * @param page
     * @return
     */
    private StringBuilder getOrderSql(Page<NotifyHisBean> page) {
        StringBuilder hql = new StringBuilder();
        Map<String, String> condMap = page.getOrder();
        if (condMap.size() != 0) {
            hql.append(" order by ");
            Iterator<String> keyItr = condMap.keySet().iterator();
            while (keyItr.hasNext()) {
                String key = keyItr.next();
                if (key.equals("time")) {
                    hql.append("n.time").append(" ").append(condMap.get(key)).append(" , ");
                }
                if (key.equals("mode")) {
                    hql.append("n.notify_mode").append(" ").append(condMap.get(key)).append(" , ");
                }
            }
            hql.delete(hql.lastIndexOf(" , "), hql.length());
        }
        return hql;
    }

    private StringBuilder getResTypeSql(String resTypeId) {
        StringBuilder sb = new StringBuilder();
        if (resTypeId != null && !"".equals(resTypeId)) {
            sb.append(" and e.res_id in(");
            sb.append(" select distinct id from res res where res.res_type_id in (");
            sb.append(" select id from res_type start with id='");
            sb.append(resTypeId);
            sb.append("' connect by prior id=parent_id");
            sb.append(" ))");
        }
        return sb;
    }

    private StringBuilder getDimensionSql(List<String> dimensionArray) {
        StringBuilder sb = new StringBuilder();
        if (dimensionArray.size() != 0) {
            sb.append(" and e.res_id in (");
            sb.append(" select distinct res_id from res_attr_val where (");
            for (String dimension : dimensionArray) {
                sb.append("attr_id in(select id from attribute where data_type_id=5 and data_unit=1) and attr_value in(");
                sb.append("select to_char(car.id) from ci_attr_range car start with id='");
                sb.append(dimension);
                sb.append("' connect by prior id=parent_id");
                sb.append(") and ");
            }
            sb.delete(sb.lastIndexOf(" and"), sb.length());
            sb.append("))");
        }
        return sb;
    }

    public NotifyHisBean findById(Long id) {
        String format = "yyyy-MM-dd HH:mm:ss";
        SimpleDateFormat simple = new SimpleDateFormat(format);//to_char(e.occur_time,'yyyy-MM-dd hh24:mi')
        StringBuilder sb = new StringBuilder(" select n.id,");
        sb.append(" n.user_,");
        sb.append(" n.notify_mode,");
        sb.append(" to_char(n.time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" n.msg,");
        sb.append(" e.level_,");
        sb.append(" e.status,");
        sb.append(" to_char(e.occur_time,'yyyy-MM-dd hh24:mi:ss'),");
        sb.append(" r.name");
        sb.append(" from notify_his n");
        sb.append(" left outer join event e on e.id = n.event_id");
        sb.append(" left outer join res r on r.id = e.res_id");
        sb.append(" where n.id =");
        sb.append(id);
        Query query = getSession().createSQLQuery(sb.toString());
        @SuppressWarnings("unchecked")
        List<Object[]> list = query.list();
        if (list == null || list.size() == 0) {
            return null;
        }
        NotifyHisBean notifyHisBean = new NotifyHisBean();
        NotifyHis notifyHis = new NotifyHis();
        notifyHis.setId(Long.valueOf(String.valueOf(list.get(0)[0])));
        notifyHis.setUser(String.valueOf(list.get(0)[1]));
        notifyHis.setNotifyMode(Integer.valueOf(String.valueOf(list.get(0)[2])));
        try {
            notifyHis.setTime(simple.parse(String.valueOf(list.get(0)[3])));
        } catch (ParseException e) {
            logger.error("find notify history bean by condition parse time error", e);
        }
        notifyHis.setMsg(String.valueOf(list.get(0)[4]));
        notifyHisBean.setEventLevel(Integer.valueOf(String.valueOf(list.get(0)[5])));
        notifyHisBean.setEventStatus(Integer.valueOf(String.valueOf(list.get(0)[6])));
        try {
            notifyHisBean.setEventOccurTime(simple.parse(String.valueOf(list.get(0)[7])));
        } catch (ParseException e) {
            logger.error("find notify history bean by condition parse event occur time  error", e);
        }
        notifyHisBean.setResName(String.valueOf(list.get(0)[8]));
        notifyHisBean.setNotifyHis(notifyHis);
        return notifyHisBean;
    }

    /**
     * 获取总数
     * 
     * @param condition
     * @return
     */
    private int getTotal(Condition condition, String resTypeId, List<String> dimensionArray) {
        StringBuilder sb = new StringBuilder(" select count(*) ");
        sb.append(" from notify_his n");
        sb.append(" left outer join event e on e.id = n.event_id");
        sb.append(" left outer join event_type et on e.event_type_id = et.id");
        sb.append(" left outer join res r on r.id = e.res_id");
        sb.append(" where 1 = 1 and r.deleted = 0");
        sb.append(getConditonSql(condition));
        sb.append(getResTypeSql(resTypeId));
        sb.append(getDimensionSql(dimensionArray));
        Query query = this.getSession().createSQLQuery(sb.toString());
        return ((java.math.BigDecimal) query.list().iterator().next()).intValue();
    }

    /**
     * 构建查询条件SQL
     * 
     * @param condition
     * @return
     */
    private StringBuilder getConditonSql(Condition condition) {
        StringBuilder sb = new StringBuilder();
        Map<String, String> condMap = condition.getWhere();
        if (condMap.size() != 0) {
            sb.append(" and ( ");
            Iterator<String> keyItr = condMap.keySet().iterator();
            while (keyItr.hasNext()) {
                String key = keyItr.next();
                if (key.equals("level_")) {
                    if (!condMap.get(key).contains(",")) {
                        sb.append("e.").append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        sb.append("e.").append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            sb.append(str[i]).append(",");
                        }
                        sb.delete(sb.lastIndexOf(","), sb.length()).append(" ) ");
                    }
                }
                if (key.equals("resIds")) {
                    if (!condMap.get(key).contains(",")) {
                        sb.append("(e.res_id in (select ");
                        sb.append(condMap.get(key));
                        sb.append(" from dual union (");
                        sb.append(" select end_id  from res_relation start with start_id = ").append(condMap.get(key));
                        sb.append(" and relation_type_id  in ( select  id from relation_type  start with id = 10 connect by prior  id = parent ) connect by prior  end_id = start_id");
                        sb.append(")))");
                    } else {
                        String[] str = condMap.get(key).split(",");
                        StringBuffer stringBuffer = new StringBuffer();
                        for (int i = 0; i < str.length; i++) {
                            stringBuffer.append(str[i]).append(",");
                        }
                        stringBuffer.delete(stringBuffer.lastIndexOf(","), stringBuffer.length());
                        sb.append(" e.res_id in (");
                        sb.append(" select end_id  from res_relation start with start_id in ( ");
                        sb.append(stringBuffer);
                        sb.append(") and relation_type_id  in ( select  id from relation_type  start with id = 10 connect by prior  id = parent ) connect by prior  end_id = start_id");
                        for (int i = 0; i < str.length; i++) {
                            sb.append(" union select ").append(str[i]).append(" from dual ");
                        }
                        sb.append(")");
                    }
                }
                if (key.equals("res_type_id")) {
                    if (!condMap.get(key).contains(",")) {
                        sb.append("et.").append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        sb.append("et.").append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            sb.append(str[i]).append(",");
                        }
                        sb.delete(sb.lastIndexOf(","), sb.length()).append(" ) ");
                    }
                }
                if (key.equals("user_")) {
                    if (!condMap.get(key).contains(",")) {
                        sb.append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        sb.append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            sb.append(str[i]).append(",");
                        }
                        sb.delete(sb.lastIndexOf(","), sb.length()).append(" ) ");
                    }
                }
                if (key.equals("notify_mode")) {
                    if (!condMap.get(key).contains(",")) {
                        sb.append(key).append(" in (" + condMap.get(key) + ")");
                    } else {
                        sb.append(key).append(" in (");
                        String[] str = condMap.get(key).split(",");
                        for (int i = 0; i < str.length; i++) {
                            sb.append(str[i]).append(",");
                        }
                        sb.delete(sb.lastIndexOf(","), sb.length()).append(" ) ");
                    }
                }
                if (key.equals("beginDate")) {
                    sb.append("time").append(" >= to_date('" + condMap.get(key) + "','yyyy-MM-dd hh24:mi')");
                }
                if (key.equals("endDate")) {
                    sb.append("time").append(" <= to_date('" + condMap.get(key) + "','yyyy-MM-dd hh24:mi')");
                }
                if (key.equals("event_id")) {
                    sb.append(" e.id").append(" = ").append(condMap.get(key));
                }
                if (key.equals("userId")) {
                    sb.append(" n.user_").append(" in (");
                    String[] array = condMap.get(key).split(",");
                    if (array.length != 0) {
                        for (String s : array) {
                            sb.append("'").append(s).append("',");
                        }
                    }
                    sb.delete(sb.lastIndexOf(","), sb.length()).append(")");
                }
                sb.append(" and ");
            }
            sb.delete(sb.lastIndexOf(" and "), sb.length()).append(" ) ");
        }
        return sb;
    }

}